﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Entity;

namespace DAL
{
    public class ReportService
    {
        #region 报表信息添加至数据库,并查询插入的标识列序号,尝试使用ExcuteScalar

        public int AddReportList(int settingsId,DateTime dt)
        {
            StringBuilder objBuilder = new StringBuilder();
            objBuilder.Append("insert into ReportList(SettingsId,UpdateTime) values({0},'{1}');select @@identity");            
            string sql = string.Format(objBuilder.ToString(), settingsId, dt);
            //提交到数据库
            try
            {
                return Convert.ToInt32(SQLHelper.GetSingleResult(sql));
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 查询最近30次总报表信息
        public List<Report> GetTop30()
        {
            string sql = "select top 600 ReportList.SettingsId,ReportListId,SettingsName+' '+ comment COLLATE  Chinese_PRC_CI_AS   as SettingsName,UpdateTime,TargetVelo1,RotationalSpeed1,TargetPos1,TargetDownforce1 from ReportList";
            sql += " inner join Settings on Settings.SettingsId   = ReportList.SettingsId";
            sql += " order by ReportListId desc";
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            List<Report> list = new List<Report>();
            while (objReader.Read())
            {
                list.Add(new Report()
                {
                    ReportListId= Convert.ToInt32(objReader["ReportListId"]),
                    SettingsId = Convert.ToInt32(objReader["SettingsId"]),
                    UpdateTime =  DateTime.Parse(objReader["UpdateTime"].ToString()),
                    //DateTime.Parse(objReader["UpdateTime"].ToString()),//
                    SettingsName = Convert.ToString(objReader["SettingsName"]),
                    TargetVelo1 = Convert.ToSingle(objReader["TargetVelo1"]),
                    RotationalSpeed1 = Convert.ToSingle(objReader["RotationalSpeed1"]),
                    TargetPos1 = Convert.ToSingle(objReader["TargetPos1"]),
                    TargetDownforce1 = Convert.ToSingle(objReader["TargetDownforce1"])
                });
            }
            objReader.Close();
            return list;
        }
        #endregion

        #region 查询最近30次总报表信息
        public List<Report> GetTop30(int id1,int id2, DateTime dt1, DateTime dt2)
        {
            string sql = "select  ReportList.SettingsId,ReportListId,SettingsName+' '+ comment COLLATE  Chinese_PRC_CI_AS as SettingsName,UpdateTime,TargetVelo1,RotationalSpeed1,TargetPos1,TargetDownforce1 from ReportList";
            sql += " inner join Settings on Settings.SettingsId  =ReportList.SettingsId";
            sql += " where ReportList.SettingsId between {0} and {1} and UpdateTime between '{2}' and '{3}' order by ReportListId desc";
            sql = string.Format(sql, id1, id2, dt1, dt2);
            
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            List<Report> list = new List<Report>();
            while (objReader.Read())
            {
                list.Add(new Report()
                {
                    ReportListId = Convert.ToInt32(objReader["ReportListId"]),
                    SettingsId = Convert.ToInt32(objReader["SettingsId"]),
                    UpdateTime = DateTime.Parse(objReader["UpdateTime"].ToString()),//
                    SettingsName = Convert.ToString(objReader["SettingsName"]),
                    TargetVelo1 = Convert.ToSingle(objReader["TargetVelo1"]),
                    RotationalSpeed1 = Convert.ToSingle(objReader["RotationalSpeed1"]),
                    TargetPos1 = Convert.ToSingle(objReader["TargetPos1"]),
                    TargetDownforce1 = Convert.ToSingle(objReader["TargetDownforce1"])
                });
            }
            objReader.Close();
            return list;
        }
        #endregion

        #region 通过Id查询单次报表信息,暂时未用
        public Report GetReportById(int reportListId)
        {
            string sql = "select SettingsName+' '+ comment COLLATE  Chinese_PRC_CI_AS as SettingsName ,UpdateTime,TargetVelo1,RotationalSpeed1,TargetPos1,TargetDownforce1 from ReportList";
            sql += " inner join Settings on Settings.SettingsId=ReportList.SettingsId";
            sql += " where ReportListId =" + reportListId;
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            Report objReport = null;
            while (objReader.Read())
            {
                objReport = new Report()
                {
                    SettingsName = Convert.ToString(objReader["SettingsName"]),
                    UpdateTime = DateTime.Parse(objReader["UpdateTime"].ToString()),//
                    TargetVelo1 = Convert.ToSingle(objReader["TargetVelo1"]),
                    RotationalSpeed1 = Convert.ToSingle(objReader["RotationalSpeed1"]),
                    TargetPos1 = Convert.ToSingle(objReader["TargetPos1"]),
                    TargetDownforce1 = Convert.ToSingle(objReader["TargetDownforce1"])
                };
            }
            objReader.Close();
            return objReport;
        }
        #endregion

        #region 启用事务将集合信息(200条数据)存入数据库
        /// <summary>
        /// 将集合中的对象插入到数据库
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool Import(List<ProcessData> list, int reportListId)
        {
            List<string> sqlList = new List<string>();
            StringBuilder sqlBuilder = new StringBuilder();
            list = list.OrderBy(item => item.iCount).ToList();
            sqlBuilder.Append("insert into ProcessData(ReportListId,DataPressure,DataTorque,ActVelo1,ActVelo2,ActPos1,iCount)");
            sqlBuilder.Append(" values({0},{1},{2},{3},{4},{5},{6});");
            foreach (ProcessData objProcessData in list)
            {
                string sql = string.Format(sqlBuilder.ToString(), reportListId,
                     objProcessData.DataPressure, objProcessData.DataTorque,
                    objProcessData.ActVelo1, objProcessData.ActVelo2,
                    objProcessData.ActPos1, objProcessData.iCount);
                sqlList.Add(sql);
            }
            return SQLHelper.UpdateByTran(sqlList);
        }
        #endregion








        #region 删除历史记录存入数据库
        /// <summary>
        /// 删除历史记录存入数据库
        /// </summary>
        /// <param name="t1"></param>
        /// <returns></returns>
        public bool Delete(DateTime t1)
        {
            List<string> sqlList = new List<string>();
            StringBuilder sqlBuilder = new StringBuilder();
            string ReportListId = "";
            string sql = "select ReportListId from ReportList where updatetime >=  CAST( '" + t1.ToString("yyyy-MM-dd") + "' AS DATETIME)";
            DataSet ds = SQLHelper.GetDataSet(sql);

            if (ds.Tables[0].Rows.Count > 0)
            {
                ReportListId = ds.Tables[0].Rows[0][0].ToString();
                 
                sql = "delete from ProcessData " +
                 " where ReportListId >= " + ReportListId.ToString();
                sqlList.Add(sql);

                sql = "delete from ReportList " +
                 " where ReportListId >= " + ReportListId.ToString();
                sqlList.Add(sql);
                return SQLHelper.UpdateByTran(sqlList);
            }
            else
            {

                return false;
            }
           
        }
        #endregion





        #region 删除报警历史记录存入数据库
        /// <summary>
        /// 删除历史记录存入数据库
        /// </summary>
        /// <param name="t1"></param>
        /// <returns></returns>
        public bool DeleteAlerm(DateTime t1)
        {
            List<string> sqlList = new List<string>();
            StringBuilder sqlBuilder = new StringBuilder();
            string InsertTime = "";
            string sql = "select top(1) InsertTime from AlarmData where InsertTime <= CAST( '" + t1.ToString("yyyy-MM-dd") + "' AS DATETIME) order by InsertTime desc";
            DataSet ds = SQLHelper.GetDataSet(sql);

            if (ds.Tables[0].Rows.Count > 0)
            {
                InsertTime = ds.Tables[0].Rows[0][0].ToString(); 
                sql = "delete from AlarmData " +
                 " where InsertTime <= CAST( '" + InsertTime + "' AS DATETIME)";
                sqlList.Add(sql);
                return SQLHelper.UpdateByTran(sqlList); 
            }
            else
            { 
                return false;
            } 
        }
        #endregion
         



        #region 查询曲线信息
        public List<ProcessData> GetProcessDataById(int reportListId)
        {
            string sql = "select * from ProcessData where ReportListId =" + reportListId + " order by iCount asc";
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            List<ProcessData> list = new List<ProcessData>();
            while (objReader.Read())
            {
                list.Add(new ProcessData()
                {
                    DataPressure = Convert.ToSingle(objReader["DataPressure"]),
                    DataTorque = Convert.ToSingle(objReader["DataTorque"]),
                    ActVelo1 = Convert.ToSingle(objReader["ActVelo1"]),
                    ActVelo2 = Convert.ToSingle(objReader["ActVelo2"]),
                    ActPos1 = Convert.ToSingle(objReader["ActPos1"])
                });
            }
            objReader.Close();
            return list;
        }
        #endregion

    }
}
